#include "mainwindow.h"
#include "ui_mainwindow.h"
#include <QDate>
#include <QSqlRecord>
#include <QValidator>
#include <QFileDialog>
#include <QTextStream>
#include <QDir>
#include <QStringConverter> 

MainWindow::MainWindow(QWidget *parent)
    : QMainWindow(parent)
    , ui(new Ui::MainWindow)
{
    ui->setupUi(this);
    this->setWindowTitle("C++ Qt 记账系统 (MySQL Root)");

    setupDatabase();

    if (db.isOpen()) {
        setupModel();
        
        ui->dateEdit->setDate(QDate::currentDate());
        ui->dateStartEdit->setDate(QDate::currentDate().addMonths(-1));
        ui->dateEndEdit->setDate(QDate::currentDate());
        ui->statsYearSpinBox->setValue(QDate::currentDate().year());
        
        ui->amountLineEdit->setValidator(new QDoubleValidator(0, 9999999.99, 2, this));

        refreshTable();
    }
}

MainWindow::~MainWindow()
{
    if (db.isOpen()) {
        db.close();
    }
    delete ui;
}

void MainWindow::setupDatabase()
{
    db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("localhost");
    db.setDatabaseName("bookkeeping_cpp_db");
    db.setUserName("root"); 
    db.setPassword("1933580388");

    if (!db.open()) {
        QMessageBox::critical(this, "数据库连接失败", "无法连接到数据库: " + db.lastError().text() + "\n请检查MySQL服务是否运行，以及用户名和密码是否正确。");
        this->setEnabled(false);
    } else {
        qDebug() << "数据库连接成功!";
    }
}

void MainWindow::setupModel()
{
    model = new QSqlTableModel(this, db);
    model->setTable("expenses");
    model->setEditStrategy(QSqlTableModel::OnManualSubmit);

    model->setHeaderData(model->fieldIndex("id"), Qt::Horizontal, "ID");
    model->setHeaderData(model->fieldIndex("date"), Qt::Horizontal, "日期");
    model->setHeaderData(model->fieldIndex("amount"), Qt::Horizontal, "金额");
    model->setHeaderData(model->fieldIndex("main_category"), Qt::Horizontal, "大分类");
    model->setHeaderData(model->fieldIndex("sub_category"), Qt::Horizontal, "二级分类");
    model->setHeaderData(model->fieldIndex("payment_method"), Qt::Horizontal, "支付方式");
    model->setHeaderData(model->fieldIndex("notes"), Qt::Horizontal, "备注");

    ui->tableView->setModel(model);
    ui->tableView->setSelectionBehavior(QAbstractItemView::SelectRows);
    ui->tableView->setEditTriggers(QAbstractItemView::NoEditTriggers);
    ui->tableView->hideColumn(model->fieldIndex("id"));
    ui->tableView->resizeColumnsToContents();
    ui->tableView->setSortingEnabled(true);
}

void MainWindow::refreshTable(const QString &filter)
{
    model->setFilter(filter);
    if (!model->select()) {
        QMessageBox::warning(this, "查询失败", "无法从数据库刷新数据: " + model->lastError().text());
    }
    model->sort(model->fieldIndex("date"), Qt::DescendingOrder);
}

void MainWindow::clearInputFields()
{
    ui->dateEdit->setDate(QDate::currentDate());
    ui->amountLineEdit->clear();
    ui->mainCategoryLineEdit->clear();
    ui->subCategoryLineEdit->clear();
    ui->paymentMethodLineEdit->clear();
    ui->notesLineEdit->clear();
    ui->tableView->clearSelection();
}

void MainWindow::on_tableView_clicked(const QModelIndex &index)
{
    if (!index.isValid()) return;
    
    QSqlRecord record = model->record(index.row());
    ui->dateEdit->setDate(record.value("date").toDate());
    ui->amountLineEdit->setText(record.value("amount").toString());
    ui->mainCategoryLineEdit->setText(record.value("main_category").toString());
    ui->subCategoryLineEdit->setText(record.value("sub_category").toString());
    ui->paymentMethodLineEdit->setText(record.value("payment_method").toString());
    ui->notesLineEdit->setText(record.value("notes").toString());
}

void MainWindow::on_addButton_clicked()
{
    QDate date = ui->dateEdit->date();
    bool ok;
    double amount = ui->amountLineEdit->text().toDouble(&ok);
    QString mainCategory = ui->mainCategoryLineEdit->text().trimmed();

    if (mainCategory.isEmpty() || !ok || amount <= 0) {
        QMessageBox::warning(this, "输入无效", "金额必须是正数且大分类不能为空。");
        return;
    }

    QSqlQuery query(db);
    query.prepare("INSERT INTO expenses (date, amount, main_category, sub_category, payment_method, notes) "
                  "VALUES (:date, :amount, :main_category, :sub_category, :payment_method, :notes)");
    query.bindValue(":date", date.toString("yyyy-MM-dd"));
    query.bindValue(":amount", amount);
    query.bindValue(":main_category", mainCategory);
    query.bindValue(":sub_category", ui->subCategoryLineEdit->text().trimmed());
    query.bindValue(":payment_method", ui->paymentMethodLineEdit->text().trimmed());
    query.bindValue(":notes", ui->notesLineEdit->text().trimmed());

    if (query.exec()) {
        QMessageBox::information(this, "成功", "新账目已成功添加。");
        refreshTable(model->filter());
        clearInputFields();
    } else {
        QMessageBox::critical(this, "错误", "添加账目失败: " + query.lastError().text());
    }
}

void MainWindow::on_editButton_clicked()
{
    QModelIndexList selectedRows = ui->tableView->selectionModel()->selectedRows();
    if (selectedRows.isEmpty()) {
        QMessageBox::warning(this, "未选择", "请先在表格中选择要修改的行。");
        return;
    }
    int selectedRow = selectedRows.first().row();
    int id = model->record(selectedRow).value("id").toInt();
    
    QDate date = ui->dateEdit->date();
    bool ok;
    double amount = ui->amountLineEdit->text().toDouble(&ok);
    QString mainCategory = ui->mainCategoryLineEdit->text().trimmed();
    
    if (mainCategory.isEmpty() || !ok || amount <= 0) {
        QMessageBox::warning(this, "输入无效", "金额必须是正数且大分类不能为空。");
        return;
    }

    QSqlQuery query(db);
    query.prepare("UPDATE expenses SET date = :date, amount = :amount, main_category = :main_category, "
                  "sub_category = :sub_category, payment_method = :payment_method, notes = :notes "
                  "WHERE id = :id");
    query.bindValue(":date", date.toString("yyyy-MM-dd"));
    query.bindValue(":amount", amount);
    query.bindValue(":main_category", mainCategory);
    query.bindValue(":sub_category", ui->subCategoryLineEdit->text().trimmed());
    query.bindValue(":payment_method", ui->paymentMethodLineEdit->text().trimmed());
    query.bindValue(":notes", ui->notesLineEdit->text().trimmed());
    query.bindValue(":id", id);

    if (query.exec()) {
        QMessageBox::information(this, "成功", "记录已成功修改。");
        refreshTable(model->filter());
        clearInputFields();
    } else {
        QMessageBox::critical(this, "错误", "修改记录失败: " + query.lastError().text());
    }
}

void MainWindow::on_deleteButton_clicked()
{
    QModelIndexList selectedRows = ui->tableView->selectionModel()->selectedRows();
    if (selectedRows.isEmpty()) {
        QMessageBox::warning(this, "未选择", "请先在表格中选择要删除的行。");
        return;
    }
    int selectedRow = selectedRows.first().row();
    int id = model->record(selectedRow).value("id").toInt();

    QMessageBox::StandardButton reply;
    reply = QMessageBox::question(this, "确认删除", "你确定要删除这条记录吗？此操作不可撤销。",
                                  QMessageBox::Yes|QMessageBox::No);
    if (reply == QMessageBox::No) {
        return;
    }

    QSqlQuery query(db);
    query.prepare("DELETE FROM expenses WHERE id = :id");
    query.bindValue(":id", id);

    if (query.exec()) {
        QMessageBox::information(this, "成功", "记录已成功删除。");
        refreshTable(model->filter());
        clearInputFields();
    } else {
        QMessageBox::critical(this, "错误", "删除记录失败: " + query.lastError().text());
    }
}

void MainWindow::on_queryButton_clicked()
{
    QStringList filters;

    if (ui->dateStartCheckBox->isChecked() && ui->dateStartEdit->date().isValid()) {
        filters << QString("date >= '%1'").arg(ui->dateStartEdit->date().toString("yyyy-MM-dd"));
    }
    if (ui->dateEndCheckBox->isChecked() && ui->dateEndEdit->date().isValid()) {
        filters << QString("date <= '%1'").arg(ui->dateEndEdit->date().toString("yyyy-MM-dd"));
    }
    if (!ui->queryCategoryLineEdit->text().trimmed().isEmpty()) {
        filters << QString("main_category LIKE '%%1%'").arg(ui->queryCategoryLineEdit->text().trimmed());
    }

    refreshTable(filters.join(" AND "));
}

void MainWindow::on_statsButton_clicked()
{
    int year = ui->statsYearSpinBox->value();
    int month = ui->statsMonthSpinBox->value();

    QSqlQuery query(db);
    QString queryString = "SELECT main_category, SUM(amount) FROM expenses WHERE YEAR(date) = :year";
    if (month > 0 && month <= 12) {
        queryString += " AND MONTH(date) = :month";
    }
    queryString += " GROUP BY main_category ORDER BY SUM(amount) DESC";

    query.prepare(queryString);
    query.bindValue(":year", year);
    if (month > 0 && month <= 12) {
        query.bindValue(":month", month);
    }
    
    if (!query.exec()) {
        QMessageBox::critical(this, "统计失败", "查询统计数据时出错: " + query.lastError().text());
        return;
    }

    if (!query.first()) {
        QMessageBox::information(this, "无记录", QString("在 %1年%2月 没有找到任何消费记录。").arg(year).arg(month > 0 ? QString::number(month) : "全年"));
        return;
    }
    
    query.previous();

    QString statsResult = QString("### %1年%2月 消费统计 ###\n\n").arg(year).arg(month > 0 ? QString::number(month) : "全年");
    double total = 0;
    while (query.next()) {
        QString category = query.value(0).toString();
        double sum = query.value(1).toDouble();
        total += sum;
        // --- 这是修正的地方 ---
        // 将 "%1: %.2f 元\n" 改为 "%1: %2 元\n"
        // 并使用 arg 的重载来格式化浮点数
        statsResult += QString("%1: %2 元\n").arg(category).arg(sum, 0, 'f', 2);
    }
    
    // --- 这里也是修正的地方 ---
    // 将 "总计: %.2f 元" 改为 "总计: %1 元"
    statsResult += QString("\n--------------------------\n总计: %1 元").arg(total, 0, 'f', 2);

    QMessageBox::information(this, "消费统计结果", statsResult);
}




void MainWindow::on_refreshButton_clicked()
{
    refreshTable("");
    clearInputFields();

    ui->dateStartCheckBox->setChecked(false);
    ui->dateEndCheckBox->setChecked(false);
    ui->queryCategoryLineEdit->clear();
}

void MainWindow::on_importCsvButton_clicked()
{
    QString defaultPath = QDir::homePath() + "/source.csv";
    QString filePath = QFileDialog::getOpenFileName(this, "选择CSV文件", defaultPath, "CSV 文件 (*.csv);;所有文件 (*)");
    
    if (filePath.isEmpty()) {
        return;
    }

    QMessageBox::StandardButton reply;
    reply = QMessageBox::question(this, "确认导入", "是否要在导入前清空数据库中所有现有账目？",
                                  QMessageBox::Yes|QMessageBox::No|QMessageBox::Cancel);

    if (reply == QMessageBox::Cancel) {
        return; 
    }

    if (reply == QMessageBox::Yes) {
        QSqlQuery clearQuery(db);
        if (!clearQuery.exec("DELETE FROM expenses")) {
            QMessageBox::critical(this, "错误", "清空数据表失败: " + clearQuery.lastError().text());
            return;
        }
    }

    QFile file(filePath);
    if (!file.open(QIODevice::ReadOnly | QIODevice::Text)) {
        QMessageBox::critical(this, "错误", "无法打开文件: " + file.errorString());
        return;
    }

    QTextStream in(&file);
    in.setEncoding(QStringConverter::Encoding::Utf8);

    if (!in.atEnd()) {
        in.readLine();
    }

    int successCount = 0;
    int failCount = 0;
    db.transaction();

    QSqlQuery query(db);
    query.prepare("INSERT INTO expenses (date, amount, main_category, sub_category, payment_method, notes) "
                  "VALUES (:date, :amount, :main_category, :sub_category, :payment_method, :notes)");

    while (!in.atEnd()) {
        QString line = in.readLine();
        QStringList fields = line.split(',');

        if (fields.count() < 6) {
            failCount++;
            continue;
        }

        QDate date = QDate::fromString(fields[0], "yyyy/M/d");
        bool ok;
        double amount = fields[1].toDouble(&ok);

        if (!date.isValid() || !ok) {
            failCount++;
            continue;
        }

        query.bindValue(":date", date.toString("yyyy-MM-dd"));
        query.bindValue(":amount", amount);
        query.bindValue(":main_category", fields[2]);
        query.bindValue(":sub_category", fields[3]);
        query.bindValue(":payment_method", fields[4]);
        query.bindValue(":notes", fields[5]);

        if (query.exec()) {
            successCount++;
        } else {
            failCount++;
            qDebug() << "插入失败:" << query.lastError().text() << "数据:" << line;
        }
    }

    db.commit();
    file.close();

    QMessageBox::information(this, "导入完成", QString("数据导入完成。\n\n成功导入: %1 条记录\n失败或跳过: %2 条记录").arg(successCount).arg(failCount));

    refreshTable();
}
